import random
import xlwt
import time
import xlrd
import pymysql
import requests
import sqlutils.dbconfig as db
from PIL import Image
from utils.time_util import stamp_ms, YYYYMMDD2, fmt_d

parentPath = r'C:\Users\da\Desktop\\'  # 全局父路径
secondPath = 'images\\' + fmt_d(None, YYYYMMDD2) + '\\thumb_img\\'
parentTitle = r'商品名称'  # 第一行商品名称需要去除

connect = pymysql.Connect(
    host=db.mHost,
    port=db.mPort,
    user=db.mUser,
    passwd=db.mPasswd,
    db=db.mDB,
    charset=db.mCharset
)


def setGoodsType():
    # 第一步
    # 设置商品分类

    count = 0
    rbook = xlrd.open_workbook(parentPath + '最终售价.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)

    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('sheet 1')
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            if containAny(['礼盒', '礼品', '礼物', '送女友', '送男友', '男朋友', '女朋友'], row[0].value):
                sheet.write(count, 1, 357)
                # print(row[0].value + "         节日礼盒")
            elif containAny(['特产', '地方特产', '地方特色'], row[0].value):
                sheet.write(count, 1, 6)
                # print(row[0].value + "         地方特产")
            elif containAny(['可乐', '咖啡', '饮料', '奶粉', '冲剂', '速溶', '咖啡粉', '奶茶', '冲泡', '麦片', '冲泡', '饮品'],
                            row[0].value):
                sheet.write(count, 1, 3)
                # print(row[0].value + "         酒水饮料")
            elif containAny(['蜜饯', '果脯', '坚果', '巴旦木', '夏威夷果', '开心果', '果干', '花生', '核桃', '葡萄干', '地瓜干'], row[0].value):
                sheet.write(count, 1, 2)
                # print(row[0].value + "         坚果蜜饯")
            elif containAny(['方便面', '螺蛳粉', '速食', '即食', '方便', '粉丝', '面', '香肠', '火锅', '熟食', '调料'], row[0].value):
                sheet.write(count, 1, 4)
                # print(row[0].value + "         方便速食")
            elif containAny(
                    ['休闲', '饼干', '蛋卷', '巧克力', '零食', '水果糖', '糖', '薯片', '膨化', '点心', '蛋糕', '糕点', '海鲜', '鱼干', '海产', '虾干'],
                    row[0].value):
                sheet.write(count, 1, 1)
                # print(row[0].value + "         休闲零食")
        count = count + 1
    wbk.save(r'C:\Users\da\Desktop\type.xls')
    print("结束")


def insertBaseMes():
    # 第二步
    # 插入商品基本信息

    goods_sn_str = 'MDZ'
    goods_sn_index = 1
    rbook = xlrd.open_workbook(parentPath + '最终售价.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)
    cursor = connect.cursor()

    isReal = '1'  # 是否实品
    onSale = '1'  # 是否销售
    aloneSale = '1'  # 是否单独销售
    integral = '0'  # 购买时可用积分数量
    sortOrder = '100'  # 商品显示顺序
    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            selectGoods = r"SELECT * FROM ecs_goods WHERE goods_name='" + row[0].value + "'"
            cursor.execute(selectGoods)
            goods = cursor.fetchall()
            if len(goods) == 0:
                goodSn = goods_sn_str + str(goods_sn_index).rjust(6, '0')
                goodsName = str(row[0].value)
                catId = str(int(row[1].value))
                goodsNumber = '999'
                marketPrice = str(row[8].value)
                shoPrice = str(row[9].value)
                goodDesc = str(row[3].value)

                ranNumber = random.randint(50, 100)
                commentCount = str(ranNumber)
                salesCount = str(ranNumber + random.randint(30, 80))

                ranTime = random.randint(1627993660, 1641040060)
                addTime = str(ranTime)
                lastUpdate = str(ranTime + random.randint(5000, 10000))

                insert = r"INSERT INTO ecs_goods(cat_id,goods_sn,goods_name,goods_number,market_price,shop_price,goods_desc,is_real,is_on_sale,is_alone_sale,integral,sort_order,add_time,last_update,comments_number,sales_count) " \
                         r"VALUES ('" + catId + "','" + goodSn + "','" + goodsName + "','" + goodsNumber + "','" + marketPrice + "','" + shoPrice + "','" + goodDesc + "','" + isReal + "','" + onSale + "','" + aloneSale + "','" + integral + "','" + sortOrder + "','" + addTime + "','" + lastUpdate + "','" + commentCount + "','" + salesCount + "')"
                cursor.execute(insert)
                goods_sn_index = goods_sn_index + 1
    print("结束")


def saveGoodsThumb():
    # 第三步
    # 存储商品缩略图
    # 存储商品网络图

    count = 1
    rbook = xlrd.open_workbook(parentPath + '最终售价.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)
    cursor = connect.cursor()

    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            # 查询goods_id
            sql = r"SELECT goods_id FROM ecs_goods WHERE goods_name='" + str(row[0].value) + "'"
            cursor.execute(sql)

            # 存在该商品时处理该商品的图片
            goods = cursor.fetchall()
            if len(goods) != 0:
                goodId = str(goods[0][0])
                nowTime = str(stamp_ms())
                m1 = secondPath + goodId + '_thumb_G_' + nowTime + '.jpg'
                m0 = secondPath + goodId + '_thumb_P_' + nowTime + '.jpg'
                time.sleep(1)
                m2 = secondPath + goodId + '_thumb_P_' + str(stamp_ms()) + '.jpg'
                time.sleep(1)
                m3 = secondPath + goodId + '_thumb_P_' + str(stamp_ms()) + '.jpg'
                time.sleep(1)
                m4 = secondPath + goodId + '_thumb_P_' + str(stamp_ms()) + '.jpg'

                saveimg(path=parentPath + m1, url=row[4].value)  # 首页缩略图
                saveimg(path=parentPath + m0, url=row[4].value)  # 详情页缩略图
                saveimg(path=parentPath + m2, url=row[5].value)
                saveimg(path=parentPath + m3, url=row[6].value)
                saveimg(path=parentPath + m4, url=row[7].value)

                # 添加图片记录
                if row[4].value != "":
                    sql4 = r"UPDATE ecs_goods SET goods_thumb='" + m1 + "',goods_img='" + row[
                        4].value + "',original_img='" + row[4].value + "' WHERE goods_id=" + goodId
                    cursor.execute(sql4)

                    sql8 = r"INSERT INTO ecs_goods_gallery (goods_id,img_url,thumb_url,img_original) VALUES (" + goodId + ",'" + \
                           row[4].value + "','" + m1 + "','" + row[4].value + "')"
                    cursor.execute(sql8)

                if row[5].value != "":
                    sql5 = r"INSERT INTO ecs_goods_gallery (goods_id,img_url,thumb_url,img_original) VALUES (" + goodId + ",'" + \
                           row[5].value + "','" + m2 + "','" + row[5].value + "')"
                    cursor.execute(sql5)

                if row[6].value != "":
                    sql6 = r"INSERT INTO ecs_goods_gallery (goods_id,img_url,thumb_url,img_original) VALUES (" + goodId + ",'" + \
                           row[6].value + "','" + m3 + "','" + row[6].value + "')"
                    cursor.execute(sql6)

                if row[7].value != "":
                    sql7 = r"INSERT INTO ecs_goods_gallery (goods_id,img_url,thumb_url,img_original) VALUES (" + goodId + ",'" + \
                           row[7].value + "','" + m4 + "','" + row[7].value + "')"
                    cursor.execute(sql7)
                print("第" + str(count) + "个结束")
                count = count + 1
    changeImg()
    print("结束")


def saveimg(path, url):
    # 存储为缩略图
    if url != "":
        img = requests.get(url)
        f = open(path, 'wb')  # 存储图片，多媒体文件需要参数b（二进制文件）
        f.write(img.content)  # 多媒体存储content
        f.close()


def changeImg():
    # 裁剪图片
    fileName = os.listdir(parentPath + secondPath)
    width = 220
    height = 220
    # os.makedirs(r'C:\Users\da\Desktop\new\images\202112\thumb_img')
    for img in fileName:
        pic = Image.open(parentPath + secondPath + img)
        newpic = pic.resize((width, height), Image.ANTIALIAS)
        newpic.save(parentPath + "new\\" + secondPath + img)


def containAny(array, str):
    # 判断包含关系
    for c in array:
        if c in str:
            return True
    return False


if __name__ == '__main__':
    # setGoodsType()
    # insertBaseMes()
    saveGoodsThumb()
